﻿using Dapper;
using System;
using System.Linq;
using SilenthinkContract;
using SilenthinkContract.Domain;
using System.Collections.Generic;

namespace SilenthinkService
{
    public class ExtraService : ServiceBase, IExtraService
    {
        #region Registor

        public bool CreateRegisterLog(RegisterLog o)
        {
            var builder = new SqlBuilder()
                .Field(
                    "([Id],[Origin],[UserName],[Password],[UserAgent],[Browser],[IpAddress],[UrlReferrer],[TraceCode],[CreatedBy],[CreatedOn])VALUES(@Id,@Origin,@UserName,@Password,@UserAgent,@Browser,@IpAddress,@UrlReferrer,@TraceCode,@CreatedBy,@CreatedOn)")
                .Table(Database.Extra.Table.RegisterLog)
                .AddParameters(
                    new
                        {
                            o.Id,
                            o.Origin,
                            o.UserName,
                            o.Password,
                            o.UserAgent,
                            o.Browser,
                            o.IpAddress,
                            o.UrlReferrer,
                            o.TraceCode,
                            o.CreatedBy,
                            o.CreatedOn
                        });

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Extra))
            {
                return Execute(connection, template) == 1;
            }
        }

        public RegisterLog GetRegisterLog(Guid id)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Extra.Table.RegisterLog)
                .Where("[Id] = @Id", new {id})
                .Where("[CreatedOn]>dateadd(n,-30,getdate())")
                .OrderBy("CreatedOn DESC");

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Extra))
            {
                return Query<RegisterLog>(connection, template).FirstOrDefault();
            }
        }

        #endregion

        #region EmailVerification

        public bool CreateEmailVerification(EmailVerification o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Extra.Table.VerificationEmail)
                .Field(
                    "([Id],[UserId],[Category],[Email],[AuthCode],[CreatedBy],[CreatedOn])VALUES(@Id,@UserId,@Category,@Email,@AuthCode,@CreatedBy,@CreatedOn)")
                .AddParameters(new {o.Id, o.UserId, o.Category, o.Email, o.AuthCode, o.CreatedBy, o.CreatedOn});

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Extra))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool DeleteEmailVerification(Guid id)
        {
            throw new NotImplementedException();
        }


        public EmailVerification GetEmailVerification(string authCode, int? category = null, long? userId = null)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Extra.Table.VerificationEmail)
                .Where("[CreatedOn]>dateadd(n,-30,getdate())")
                .Where("[AuthCode] = @AuthCode", new {authCode})
                .OrderBy("CreatedOn DESC");
            if (category.HasValue)
            {
                builder.Where("[Category]=@Category", new {category});
            }
            if (userId.HasValue)
            {
                builder.Where("[UserId]=@UserId", new {userId});
            }

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Extra))
            {
                return Query<EmailVerification>(connection, template).FirstOrDefault();
            }
        }

        public PageList<EmailVerification> GetPagedEmailVerification(int index = 1, int size = 20, string email = null,
                                                                     int? category = null, DateTime? beginDate = null,
                                                                     DateTime? endDate = null)
        {
            throw new NotImplementedException();
        }

        #endregion

        #region SmsVerification

        public bool CreateSmsVerification(SmsVerification o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Extra.Table.VerificationSms)
                .Field(
                    "([Id],[UserId],[Category],[Mobile],[SmsCode],[CreatedBy],[CreatedOn])VALUES(@Id,@UserId,@Category,@Mobile,@SmsCode,@CreatedBy,@CreatedOn)")
                .AddParameters(new {o.Id, o.UserId, o.Category, o.Mobile, o.SmsCode, o.CreatedBy, o.CreatedOn});

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Extra))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool DeleteSmsVerification(Guid id)
        {
            throw new NotImplementedException();
        }

        public SmsVerification GetSmsVerification(string smsCode, string mobile, int? category = null,
                                                  long? userId = null)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Extra.Table.VerificationSms)
                .Where("[SmsCode] = @SmsCode", new {smsCode})
                .Where("[Mobile]=@Mobile", new {mobile})
                .Where("[CreatedOn]>dateadd(n,-30,getdate())")
                .OrderBy("CreatedOn DESC");
            if (userId.HasValue)
            {
                builder.Where("[UserId]=@UserId", new {userId});
            }
            if (category.HasValue)
            {
                builder.Where("[Category]=@Category", new {category});
            }

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Extra))
            {
                return Query<SmsVerification>(connection, template).FirstOrDefault();
            }
        }

        public PageList<SmsVerification> GetPagedSmsVerification(int index = 1, int size = 20, string smsCode = null,
                                                                 long? userId = new long?(), int? category = new int?(),
                                                                 DateTime? beginDate = new DateTime?(),
                                                                 DateTime? endDate = new DateTime?())
        {
            throw new NotImplementedException();
        }

        #endregion


        #region Implementation of IService

        public int GetCachedSQLCount()
        {
            return SqlMapper.GetCachedSQLCount();
        }

        public List<Tuple<string, string, int>> GetCachedSQL()
        {
            return SqlMapper.GetCachedSQL().ToList();
        }

        public void PurgeQueryCache()
        {
            SqlMapper.PurgeQueryCache();
        }

        #endregion

    }
}